package com.whsw.scheduler.trading_center_scheduler.scheduler.reportFormScheduler;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.DSFactory;
import com.whsw.scheduler.trading_center_scheduler.entity.PubTResultLetter;
import com.whsw.scheduler.trading_center_scheduler.util.SchedulerUtil;
import com.whsw.scheduler.trading_center_scheduler.vo.DataStatisticsInfoVO;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.util.ObjectUtils;

import java.util.List;

/**
 * 数据统计定时器
 * @Author liangyiqiang 2021/03/20
 * */
@Component
public class DataStatisticsScheduler {
    @Autowired
    DSFactory dsFactory;

    /**
     * 数据统计-建设工程-工程建设交易情况
     * @Author liangyiqiang 2021/03/20
     * 本次数据对接不设计 小型简易类型的数据 而小型简易类型的数据存储在jsgc_pub_t_project表中
     * */
    @Scheduled(cron = "0/10 * * * * ?")
    public void getDataStatisticsInfo() throws Exception {
        Db db1 = Db.use(dsFactory.getDataSource("a"));//中间库
        Db db2 = Db.use(dsFactory.getDataSource("b"));//测试库
        List<Entity> list1 = db2.query("" +
                "select\n" +
                "result_letter_id,\n" +
                "approval_status,\n" +
                "create_by,\n" +
                "create_time,\n" +
                "creater,\n" +
                "delete_flag,\n" +
                "update_by,\n" +
                "update_time,\n" +
                "updater,\n" +
                "bidder_name,\n" +
                "enroll_id,\n" +
                "enterprise_base_id,\n" +
                "enterprise_code,\n" +
                "enterprise_name,\n" +
                "package_id,\n" +
                "project_id,\n" +
                "social_credit_code,\n" +
                "status,\n" +
                "submit_time,\n" +
                "time_limit,\n" +
                "win_price\n" +
                "from pub_t_result_letter");

        List<Entity> voList = db2.query("" +
                "select\n" +
                "a.package_id AS package_id,\n" +
                "group_concat(CONCAT(g.province_name , g.city_name , ifnull(g.distict_name,''))) AS project_area\n" +
                "from pub_t_package a\n" +
                "left join pub_t_project b on b.project_id = a.project_id \n" +
                "left join pub_t_result_letter c on c.package_id = a.package_id \n" +
                "left join gb_t_result_letter_enterprise d on d.result_letter_id = c.result_letter_id \n" +
                "left join gb_t_project_extend f on f.project_id = b.project_id \n" +
                "left join gb_t_package_deposit e on e.package_id = a.package_id \n" +
                "left join gb_t_project_area g on g.project_id = a.project_id\n" +
                "left join pub_t_enroll h on a.package_id=h.PACKAGE_ID\n" +
                "where \n" +
                "c.approval_status = 'passed'\n" +
                "and c.status = 1\n" +
                "and e.status = 1\n" +
                "group by a.project_id");

        if(!ObjectUtils.isEmpty(list1) && !ObjectUtils.isEmpty(voList)){
            for (Entity entity : list1) {
                for (Entity vo : voList) {
                    if(!ObjectUtils.isEmpty(vo.get("package_id")) && !ObjectUtils.isEmpty(entity.get("package_id"))){
                        String voId = (String)(vo.get("package_id"));
                        String entityId = (String)(entity.get("package_id"));
                        if(voId.equals(entityId)){//判断voList每一个元素中的packageId是否等于list1每一个元素中的packageId
                            if(!ObjectUtils.isEmpty(vo.get("project_area"))){
                                String str = (String)(vo.get("project_area"));
                                if(SchedulerUtil.isLocal(str)){
                                    entity.put("project_classify","local");
                                }else{
                                    entity.put("project_classify","nonLocal");
                                }
                            }
                        }
                    }
                }
            }
        }

        if(!ObjectUtils.isEmpty(list1)){
            db1.del(
                    Entity.create("jsgc_pub_t_result_letter")
            );
            for (Entity entity : list1) {
                entity.setTableName("jsgc_pub_t_result_letter");
            }
            db1.insert(list1);
        }

        List<Entity> list2 = db2.query("" +
                "select\n" +
                " package_deposit_id,\n" +
                "approval_status,\n" +
                "create_by,\n" +
                "create_time,\n" +
                "creater,\n" +
                "delete_flag,\n" +
                "update_by,\n" +
                "update_time,\n" +
                "updater,\n" +
                "amount,\n" +
                "charging_method,\n" +
                "control_price,\n" +
                "deposit_form,\n" +
                "is_charge,\n" +
                "package_id,\n" +
                "predicted_contract_price,\n" +
                "status,\n" +
                "submit_time,\n" +
                "modify_source,\n" +
                "bid_validity\n" +
                "from gb_t_package_deposit");
        if(!ObjectUtils.isEmpty(list2)){
            db1.del(
                    Entity.create("jsgc_gb_t_package_deposit")
            );
            for (Entity entity : list2) {
                entity.setTableName("jsgc_gb_t_package_deposit");
            }
            db1.insert(list2);
        }

        List<Entity> list3 = db2.query("" +
                "select\n" +
                " project_id,\n" +
                "approval_status,\n" +
                "create_by,\n" +
                "create_time,\n" +
                "creater,\n" +
                "delete_flag,\n" +
                "update_by,\n" +
                "update_time,\n" +
                "updater,\n" +
                "agent,\n" +
                "agent_contact,\n" +
                "agent_contact_number,\n" +
                "agent_id,\n" +
                "approval_code,\n" +
                "approval_name,\n" +
                "approve_dept_code,\n" +
                "approve_dept_name,\n" +
                "budget_amount,\n" +
                "business_type,\n" +
                "city_percent,\n" +
                "collective_percent,\n" +
                "data_source,\n" +
                "district_percent,\n" +
                "elec_ev,\n" +
                "elec_qual,\n" +
                "electrization,\n" +
                "enroll_way,\n" +
                "foreign_capital_percent,\n" +
                "industry,\n" +
                "industry_detail,\n" +
                "invest_project_codet,\n" +
                "invest_project_codet_relation,\n" +
                "open_way,\n" +
                "private_percent,\n" +
                "project_address,\n" +
                "project_area,\n" +
                "project_code,\n" +
                "project_name,\n" +
                "province_percent,\n" +
                "qualification_type,\n" +
                "re_pub_project_id,\n" +
                "retendering,\n" +
                "sell_file,\n" +
                "state_percent,\n" +
                "status,\n" +
                "submit_time,\n" +
                "supervise_dept_code,\n" +
                "supervise_dept_name,\n" +
                "tender_category,\n" +
                "tender_category_detail,\n" +
                "tender_contact,\n" +
                "tender_contact_number,\n" +
                "tender_content,\n" +
                "tender_form,\n" +
                "tender_id,\n" +
                "tender_name,\n" +
                "tender_property,\n" +
                "tender_type,\n" +
                "tender_way,\n" +
                "tendering_count,\n" +
                "enterprise_db,\n" +
                "model,\n" +
                "tender_socialcredit_code,\n" +
                "invest_project_code,\n" +
                "invest_project_code_relation,\n" +
                "finance_percent,\n" +
                "finance,\n" +
                "finance_level,\n" +
                "stationing_enterprise,\n" +
                "owner_contact,\n" +
                "owner_contact_number,\n" +
                "owner_id,\n" +
                "owner_name,\n" +
                "owner_number,\n" +
                "owner_socialcredit_code\n" +
                "from\n" +
                "pub_t_project");
        if(!ObjectUtils.isEmpty(list3)){
            db1.del(
                    Entity.create("jsgc_pub_t_project")
            );
            for (Entity entity : list3) {
                entity.setTableName("jsgc_pub_t_project");
            }
            db1.insert(list3);
        }
    }

    /**
     * 数据统计-建设工程-招标人数前三位项目情况
     * @Author liangyiqiang 2021/03/25
     * 需要额外字段：
     * enterprise_name,企业名称
     * bid_people_num,中标人数
     *
     * 疑问；
     * 1.a.package_id项目id是什么数据类型
     * */
    @Scheduled(cron = "0/10 * * * * ?")
    public void getTopThreeBidPeopleNumber() throws Exception {
        Db db1 = Db.use(dsFactory.getDataSource("a"));//中间库
        Db db2 = Db.use(dsFactory.getDataSource("b"));//测试库
        List<Entity> list = db2.query("" +
                "select" +
                " a.package_code AS project_code," +
                "a.package_name AS project_name," +
                "a.create_by AS creater," +
                "a.package_id AS project_id," +
                "b.tender_category AS enterprise_type," +
                "sum(d.price) AS bid_price," +
                "c.submit_time AS create_time\n" +
                "from pub_t_package a\n" +
                "left join pub_t_project b on b.project_id = a.project_id \n" +
                "left join pub_t_result_letter c on c.package_id = a.package_id \n" +
                "left join gb_t_result_letter_enterprise d on d.result_letter_id = c.result_letter_id \n" +
                "left join gb_t_project_extend f on f.project_id = b.project_id \n" +
                "left join gb_t_package_deposit e on e.package_id = a.package_id \n" +
                "left join gb_t_project_area g on g.project_id = a.project_id \n" +
                "where \n" +
                "c.approval_status = 'passed'\n" +
                "and c.status = 1\n" +
                "and e.status = 1\n" +
                "group by a.project_id  ");
        if(!ObjectUtils.isEmpty(list)){
            db1.del(
                    Entity.create("win_bid_comparison")
            );
            for (Entity entity : list) {
                entity.setTableName("win_bid_comparison");
            }
            db1.insert(list);
        }
    }


    /**
     * 数据统计-市建筑业联合协会
     * @Author liangyiqiang 2021/03/25
     * 需要额外字段：
     * acreage
     * * */
    @Scheduled(cron = "0/10 * * * * ?")
    public void getMunicipalConstructionIndustryAssociationInfo() throws Exception {
        Db db1 = Db.use(dsFactory.getDataSource("a"));//中间库
        Db db2 = Db.use(dsFactory.getDataSource("b"));//测试库
        List<Entity> list = db2.query("" +
                "select" +
                " a.package_name AS project_name," +
                "a.project_id AS project_id," +
                "a.create_by AS creater," +
                "sum(d.price) AS bid_price," +
                "c.submit_time AS create_time,\n" +
                "e.control_price AS control_price\n" +
                "from pub_t_package a\n" +
                "left join pub_t_project b on b.project_id = a.project_id \n" +
                "left join pub_t_result_letter c on c.package_id = a.package_id \n" +
                "left join gb_t_result_letter_enterprise d on d.result_letter_id = c.result_letter_id \n" +
                "left join gb_t_project_extend f on f.project_id = b.project_id \n" +
                "left join gb_t_package_deposit e on e.package_id = a.package_id \n" +
                "left join gb_t_project_area g on g.project_id = a.project_id \n" +
                "where \n" +
                "c.approval_status = 'passed'\n" +
                "and c.status = 1\n" +
                "and e.status = 1\n" +
                "group by a.project_id  ");
        if(!ObjectUtils.isEmpty(list)){
            db1.del(
                    Entity.create("municipal_construction_industry_association")
            );
            for (Entity entity : list) {
                entity.setTableName("municipal_construction_industry_association");
            }
            db1.insert(list);
        }
    }

    /**
     * 数据统计-公共资源交易数据统计表
     * @Author liangyiqiang 2021/03/25
     * 建设工程数据对接，页面功能只显示第二行数据
     * 完成
     * * */
    @Scheduled(cron = "0/10 * * * * ?")
    public void getPublicResourceTransactionInfoDataTable() throws Exception {
        Db db1 = Db.use(dsFactory.getDataSource("a"));//中间库
        Db db2 = Db.use(dsFactory.getDataSource("b"));//测试库
        List<Entity> list1 = db2.query("" +
                "select\n" +
                " before_id,\n" +
                "maintenance_id,\n" +
                "create_account,\n" +
                "update_account,\n" +
                "publish_status,\n" +
                "publish_time,\n" +
                "url,\n" +
                "win_price,\n" +
                "submit_time,\n" +
                "time_limit,\n" +
                "status,\n" +
                "bidder_name,\n" +
                "project_id,\n" +
                "enroll_id,\n" +
                "package_id,\n" +
                "result_letter_id,\n" +
                "enterprise_code,\n" +
                "social_credit_code,\n" +
                "enterprise_name,\n" +
                "enterprise_base_id,\n" +
                "updater,\n" +
                "create_time,\n" +
                "delete_flag,\n" +
                "update_time,\n" +
                "create_by,\n" +
                "update_by,\n" +
                "creater,\n" +
                "approval_status\n" +
                "from pub_t_result_letter");
        if(!ObjectUtils.isEmpty(list1)){
            db1.del(
                    Entity.create("hjk_gz_jsgc_uat_pub_t_result_letter")
            );
            for (Entity entity : list1) {
                entity.setTableName("hjk_gz_jsgc_uat_pub_t_result_letter");
            }
            db1.insert(list1);
        }

        List<Entity> list2 = db2.query("" +
                "select \n" +
                " project_id,\n" +
                "approval_status,\n" +
                "create_by,\n" +
                "create_time,\n" +
                "creater,\n" +
                "delete_flag,\n" +
                "update_by,\n" +
                "update_time,\n" +
                "updater,\n" +
                "agent,\n" +
                "agent_contact,\n" +
                "agent_contact_number,\n" +
                "agent_id,\n" +
                "approval_code,\n" +
                "approval_name,\n" +
                "approve_dept_code,\n" +
                "approve_dept_name,\n" +
                "budget_amount,\n" +
                "business_type,\n" +
                "city_percent,\n" +
                "collective_percent,\n" +
                "data_source,\n" +
                "district_percent,\n" +
                "elec_ev,\n" +
                "elec_qual,\n" +
                "electrization,\n" +
                "enroll_way,\n" +
                "foreign_capital_percent,\n" +
                "industry,\n" +
                "industry_detail,\n" +
                "invest_project_codet,\n" +
                "invest_project_codet_relation,\n" +
                "open_way,\n" +
                "private_percent,\n" +
                "project_address,\n" +
                "project_area,\n" +
                "project_code,\n" +
                "project_name,\n" +
                "province_percent,\n" +
                "qualification_type,\n" +
                "re_pub_project_id,\n" +
                "retendering,\n" +
                "sell_file,\n" +
                "state_percent,\n" +
                "status,\n" +
                "submit_time,\n" +
                "supervise_dept_code,\n" +
                "supervise_dept_name,\n" +
                "tender_category,\n" +
                "tender_category_detail,\n" +
                "tender_contact,\n" +
                "tender_contact_number,\n" +
                "tender_content,\n" +
                "tender_form,\n" +
                "tender_id,\n" +
                "tender_name,\n" +
                "tender_property,\n" +
                "tender_type,\n" +
                "tender_way,\n" +
                "tendering_count,\n" +
                "enterprise_db,\n" +
                "model,\n" +
                "tender_socialcredit_code,\n" +
                "invest_project_code,\n" +
                "invest_project_code_relation,\n" +
                "finance_percent,\n" +
                "finance,\n" +
                "finance_level,\n" +
                "stationing_enterprise,\n" +
                "owner_contact,\n" +
                "owner_contact_number,\n" +
                "owner_id,\n" +
                "owner_name,\n" +
                "owner_number,\n" +
                "owner_socialcredit_code,\n" +
                "before_id,\n" +
                "maintenance_id,\n" +
                "create_account,\n" +
                "update_account\n" +
                "from pub_t_project");
        if(!ObjectUtils.isEmpty(list2)){
            db1.del(
                    Entity.create("hjk_gz_jsgc_uat_pub_t_project")
            );
            for (Entity entity : list2) {
                entity.setTableName("hjk_gz_jsgc_uat_pub_t_project");
            }
            db1.insert(list2);
        }

        List<Entity> list3 = db2.query("" +
                "select\n" +
                " package_deposit_id,\n" +
                "approval_status,\n" +
                "create_by,\n" +
                "create_time,\n" +
                "creater,\n" +
                "delete_flag,\n" +
                "update_by,\n" +
                "update_time,\n" +
                "updater,\n" +
                "amount,\n" +
                "charging_method,\n" +
                "control_price,\n" +
                "deposit_form,\n" +
                "is_charge,\n" +
                "package_id,\n" +
                "predicted_contract_price,\n" +
                "status,\n" +
                "submit_time,\n" +
                "modify_source,\n" +
                "bid_validity,\n" +
                "before_id,\n" +
                "maintenance_id,\n" +
                "create_account,\n" +
                "update_account\n" +
                "from gb_t_package_deposit");
        if(!ObjectUtils.isEmpty(list3)){
            db1.del(
                    Entity.create("hjk_gz_jsgc_uat_gb_t_package_deposit")
            );
            for (Entity entity : list3) {
                entity.setTableName("hjk_gz_jsgc_uat_gb_t_package_deposit");
            }
            db1.insert(list3);
        }
    }
}
